/**
 * Prisma迁移示例
 * 展示如何将mysql2查询替换为Prisma查询
 */

const { query } = require('../config/db'); // 原有的mysql2方式
const { prisma } = require('../config/prisma'); // 新的Prisma方式

// ==================== 用户查询示例 ====================

/**
 * 示例1: 根据ID查询用户
 */

// 原有的mysql2方式
async function findUserByIdOld(id) {
  const sql = 'SELECT * FROM users WHERE id = ?';
  const rows = await query(sql, [id]);
  return rows.length > 0 ? rows[0] : null;
}

// 新的Prisma方式
async function findUserByIdNew(id) {
  return await prisma.users.findUnique({
    where: { id: BigInt(id) }
  });
}

/**
 * 示例2: 创建用户
 */

// 原有的mysql2方式
async function createUserOld(userData) {
  const sql = `
    INSERT INTO users (openid, nickname, avatar_url, phone_number, status, publishing_credits, inviter_id)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `;
  const params = [
    userData.openid,
    userData.nickname || null,
    userData.avatar_url || null,
    userData.phone_number || null,
    userData.status || 'inactive',
    userData.publishing_credits || 0,
    userData.inviter_id || null
  ];
  
  const result = await query(sql, params);
  return await findUserByIdOld(result.insertId);
}

// 新的Prisma方式
async function createUserNew(userData) {
  return await prisma.users.create({
    data: {
      openid: userData.openid,
      nickname: userData.nickname || null,
      avatar_url: userData.avatar_url || null,
      phone_number: userData.phone_number || null,
      status: userData.status || 'inactive',
      publishing_credits: userData.publishing_credits || 0,
      inviter_id: userData.inviter_id ? BigInt(userData.inviter_id) : null
    }
  });
}

/**
 * 示例3: 分页查询用户
 */

// 原有的mysql2方式
async function getUserListOld(options = {}) {
  const { page = 1, pageSize = 10, status, search } = options;
  const pageNum = parseInt(page);
  const pageSizeNum = parseInt(pageSize);
  const offset = (pageNum - 1) * pageSizeNum;

  const conditions = [];
  const params = [];

  if (status) {
    conditions.push('status = ?');
    params.push(status);
  }

  if (search) {
    conditions.push('(nickname LIKE ? OR phone_number LIKE ?)');
    params.push(`%${search}%`, `%${search}%`);
  }

  const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

  // 查询总数
  const countSql = `SELECT COUNT(*) as total FROM users ${whereClause}`;
  const countResult = await query(countSql, params);
  const total = countResult[0].total;

  // 查询列表数据
  const listSql = `
    SELECT * FROM users ${whereClause}
    ORDER BY created_at DESC
    LIMIT ${pageSizeNum} OFFSET ${offset}
  `;
  const rows = await query(listSql, params);

  return {
    data: rows,
    pagination: {
      page: pageNum,
      pageSize: pageSizeNum,
      total,
      totalPages: Math.ceil(total / pageSizeNum)
    }
  };
}

// 新的Prisma方式
async function getUserListNew(options = {}) {
  const { page = 1, pageSize = 10, status, search } = options;
  const pageNum = parseInt(page);
  const pageSizeNum = parseInt(pageSize);
  const skip = (pageNum - 1) * pageSizeNum;

  // 构建where条件
  const where = {};
  
  if (status) {
    where.status = status;
  }

  if (search) {
    where.OR = [
      { nickname: { contains: search } },
      { phone_number: { contains: search } }
    ];
  }

  // 并行查询总数和列表数据
  const [total, users] = await Promise.all([
    prisma.users.count({ where }),
    prisma.users.findMany({
      where,
      orderBy: { created_at: 'desc' },
      skip,
      take: pageSizeNum
    })
  ]);

  return {
    data: users,
    pagination: {
      page: pageNum,
      pageSize: pageSizeNum,
      total,
      totalPages: Math.ceil(total / pageSizeNum)
    }
  };
}

/**
 * 示例4: 更新用户额度
 */

// 原有的mysql2方式
async function addCreditsOld(id, credits) {
  const sql = 'UPDATE users SET publishing_credits = publishing_credits + ? WHERE id = ?';
  await query(sql, [credits, id]);
  return await findUserByIdOld(id);
}

// 新的Prisma方式
async function addCreditsNew(id, credits) {
  return await prisma.users.update({
    where: { id: BigInt(id) },
    data: {
      publishing_credits: {
        increment: credits
      }
    }
  });
}

/**
 * 示例5: 复杂查询 - 获取用户及其发布的商品
 */

// 原有的mysql2方式
async function getUserWithListingsOld(userId) {
  const userSql = 'SELECT * FROM users WHERE id = ?';
  const listingsSql = 'SELECT * FROM listings WHERE user_id = ? ORDER BY created_at DESC';
  
  const [userRows, listingsRows] = await Promise.all([
    query(userSql, [userId]),
    query(listingsSql, [userId])
  ]);

  if (userRows.length === 0) return null;

  return {
    ...userRows[0],
    listings: listingsRows
  };
}

// 新的Prisma方式
async function getUserWithListingsNew(userId) {
  return await prisma.users.findUnique({
    where: { id: BigInt(userId) },
    include: {
      listings: {
        orderBy: { created_at: 'desc' }
      }
    }
  });
}

// ==================== 主要优势对比 ====================

/**
 * Prisma相比mysql2的优势：
 * 
 * 1. 类型安全：自动生成TypeScript类型，减少运行时错误
 * 2. 智能提示：IDE自动补全，提高开发效率
 * 3. 关系查询：include和select让关联查询更简单
 * 4. 原子操作：increment/decrement等原子操作更安全
 * 5. 并发查询：Promise.all自动优化
 * 6. 防SQL注入：自动参数化查询
 * 7. 连接池管理：自动管理数据库连接
 * 8. 查询优化：自动生成优化的SQL
 * 9. 事务支持：更简单的事务API
 * 10. 迁移管理：schema变更更容易追踪
 */

module.exports = {
  // 旧方式
  findUserByIdOld,
  createUserOld,
  getUserListOld,
  addCreditsOld,
  getUserWithListingsOld,
  
  // 新方式
  findUserByIdNew,
  createUserNew,
  getUserListNew,
  addCreditsNew,
  getUserWithListingsNew
};
